﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.IO; 
using cecbData;
using cecbClasses.Data_Adapters;

namespace cecbClasses.Data_Adapters.Excel
{
    public class ReadExcel
    {
        private static Microsoft.Office.Interop.Excel.Application appExcel;
        private static Workbook newWorkbook = null;
        private static _Worksheet objsheet = null;
        Microsoft.Office.Interop.Excel.Range range;


        public ReadExcel() { 
        
        }


        public void excel_init(String path)
        {
            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
              //  MessageBox.Show("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;
               
            }

        }


        public string excel_getValue(string cellname)
        {
            string value = string.Empty;
            try
            {
                value = objsheet.get_Range(cellname).get_Value().ToString();
            }
            catch
            {
                value = "";
            }

            return value;
        }

        List<Data_Activities> actList;

        public List<Data_Activities> getAllActivities(String path)
        {

            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                //  MessageBox.Show("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;

            }
            //=========================

            actList = new List<Data_Activities>();
            range = objsheet.UsedRange;
            var str = "";
            int rCnt = 0;
            int cCnt = 0;

             
                for (cCnt = 6; cCnt <= range.Columns.Count; cCnt +=2)
                {
                    str = (range.Cells[5, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
                    // MessageBox.Show(str);
                    if (str != null)
                    {
                        if (str.Length != 0)
                            actList.Add(new Data_Activities(str));
                    }

                    if (range.Columns.Count <= cCnt) {
                        return actList;
                    }
                }
            


            return actList;
        }

        List<Data_Impacts> imptList;

        public List<Data_Impacts> getAllImpacts(String path)
        {

            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                //  MessageBox.Show("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;

            }
            //=========================


            imptList = new List<Data_Impacts>();
            range = objsheet.UsedRange;
            var str = "";
            int rCnt = 0;
            int cCnt = 0;

            for (rCnt = 6; rCnt <= range.Rows.Count; rCnt+=2)
            {
                 
                    str = (range.Cells[rCnt, 5] as Microsoft.Office.Interop.Excel.Range).Text;
                    // MessageBox.Show(str);
                    if (str != null)
                    {
                        if (str.Length != 0)
                            imptList.Add(new Data_Impacts(str));
                    }

                    if (range.Rows.Count <= rCnt)
                    {
                        return imptList;
                    }
                
            }


            return imptList;
        }

        public List<Data_LeopoldMatrixValues> getValues(String path)
        {
            //==================================

            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                //  MessageBox.Show("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;

            }
             
            //==================================
            List<Data_LeopoldMatrixValues> LeoList = new List<Data_LeopoldMatrixValues>();
            range = objsheet.UsedRange;

            var act = "";
            var impt = "";
            var val1 = "";
            var val2 = "";
            int rCnt = 0;
            int cCnt = 0;

            for (rCnt = 5; rCnt <= range.Rows.Count; rCnt+=2)
            {
                for (cCnt = 5; cCnt <= range.Columns.Count; cCnt+=2)
                {


                    act = (range.Cells[4, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
                    impt = (range.Cells[rCnt, 4] as Microsoft.Office.Interop.Excel.Range).Text;
                    val1 = (range.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
                    val2 = (range.Cells[++rCnt, ++cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
                    rCnt--;
                    cCnt--;

                    String actName = act.ToString();
                    String imptName = impt.ToString();
                    try
                    {
                         int v1 = int.Parse(val1.ToString() == ""? "0" : val1.ToString());
                         int v2 = int.Parse(val2.ToString() == "" ? "0" : val2.ToString());
                         if (actName.Length > 0)
                         {
                             if (imptName.Length > 0)
                             {
                                 LeoList.Add(new Data_LeopoldMatrixValues(v1, v2, actName, imptName));
                             }
                         }
                    }
                    catch (Exception)
                    {
                        
                        if (actName.Length > 0)
                        {
                            if (imptName.Length > 0)
                            {
                                LeoList.Add(new Data_LeopoldMatrixValues(0, 0, actName, imptName));
                            }
                        }
                        
                    }

                    
                    
                    
                }
            }


            newWorkbook.Close(true, null, null);
            appExcel.Quit();

            releaseObject(objsheet);
            releaseObject(newWorkbook);
            releaseObject(appExcel);


            return LeoList;
        }


        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
               // MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 



        List<string> lst;

        public List<String> MyReadMethode() {
            lst = new List<string>();
            range = objsheet.UsedRange;

            var str = "";
            int rCnt = 0;
            int cCnt = 0;

            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    str =  (range.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
                    // MessageBox.Show(str);
                    lst.Add(str.ToString() +  " >>  Row no: " + rCnt + " | Colomn no:  " + cCnt );
                }
            }
            return lst;
        }

        bool IsSimpleMatrix = false;

        public bool SelectMatrix() {

            return IsSimpleMatrix;
        }

        public List<Data_SimpleMatrixValues> getValuesSimpleMatrix(String path)
        {
            //==================================


           

            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                //  MessageBox.Show("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;

            }

            //==================================
            List<Data_SimpleMatrixValues> LeoList = new List<Data_SimpleMatrixValues>();
            range = objsheet.UsedRange;

            
          
            var act = "";
            var impt = "";
            var val1 = "";
            var val2 = "";
            int rCnt = 0;
            int cCnt = 0;

            for (rCnt = 6; rCnt <= range.Rows.Count; rCnt += 1)
            {
                for (cCnt = 6; cCnt <= range.Columns.Count; cCnt += 1)
                {


                    act = (range.Cells[5, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
                    impt = (range.Cells[rCnt, 5] as Microsoft.Office.Interop.Excel.Range).Text;
                    val1 = (range.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
                    //val2 = (range.Cells[++rCnt, ++cCnt] as Microsoft.Office.Interop.Excel.Range).Text;
                    //rCnt--;
                    //cCnt--;

                    String actName = act.ToString();
                    String imptName = impt.ToString();
                    int v1 = int.Parse(val1.ToString() == "" ? "0" : val1.ToString());
                 
                    if (actName.Length > 0)
                    {
                        if (imptName.Length > 0)
                        {
                            LeoList.Add(new Data_SimpleMatrixValues(v1, actName, imptName));
                        }
                    }


                }
            }


            newWorkbook.Close(true, null, null);
            appExcel.Quit();

            releaseObject(objsheet);
            releaseObject(newWorkbook);
            releaseObject(appExcel);


            return LeoList;
        }

        public bool getMatrixType(String path)
        {
            
            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                //  MessageBox.Show("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;

            }

            //==================================
             
            range = objsheet.UsedRange;

            try
            {
                String mtrxtype = (range.Cells[2, 2] as Microsoft.Office.Interop.Excel.Range).Text;
                if (mtrxtype == "Simple Matrix")
                    IsSimpleMatrix = true;
                else
                    IsSimpleMatrix = false;
            }
            catch (Exception)
            {
                IsSimpleMatrix = false;
               
            }
            newWorkbook.Close(true, null, null);
            appExcel.Quit();

            releaseObject(objsheet);
            releaseObject(newWorkbook);
            releaseObject(appExcel);


            return IsSimpleMatrix;
        }


        public String getProjectReference(String path)
        {

            appExcel = new Microsoft.Office.Interop.Excel.Application();

            if (System.IO.File.Exists(path))
            {
                // then go and load this into excel
                newWorkbook = appExcel.Workbooks.Open(path, true, true);
                objsheet = (_Worksheet)appExcel.ActiveWorkbook.ActiveSheet;
            }
            else
            {
                //  MessageBox.Show("Unable to open file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;

            }

            //==================================

            range = objsheet.UsedRange;

            try
            {
                return (range.Cells[1, 2] as Microsoft.Office.Interop.Excel.Range).Text;
                 
            }
            catch (Exception)
            {
                IsSimpleMatrix = false;

            }
            newWorkbook.Close(true, null, null);
            appExcel.Quit();

            releaseObject(objsheet);
            releaseObject(newWorkbook);
            releaseObject(appExcel);


            return "";
        }


        //public void ReadEcelRow()
        //{
        //    Microsoft.Office.Interop.Excel.Application xlApp;
        //    Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        //    Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        //    Microsoft.Office.Interop.Excel.Range range;

        //    string str;
        //    int rCnt = 0;
        //    int cCnt = 0;

        //    xlApp = new Microsoft.Office.Interop.Excel.Application();
        //    xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        //    xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //    range = xlWorkSheet.UsedRange;

        //    for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
        //    {
        //        for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
        //        {
        //            str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
        //           // MessageBox.Show(str);
        //        }
        //    }

        //    xlWorkBook.Close(true, null, null);
        //    xlApp.Quit();

        //    releaseObject(xlWorkSheet);
        //    releaseObject(xlWorkBook);
        //    releaseObject(xlApp);
        //}

        //private void releaseObject(object obj)
        //{
        //    try
        //    {
        //        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        //        obj = null;
        //    }
        //    catch (Exception ex)
        //    {
        //        obj = null;
        //        //MessageBox.Show("Unable to release the Object " + ex.ToString());
        //    }
        //    finally
        //    {
        //        GC.Collect();
        //    }
        //} 


    }
}
